package com.icim.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.icim.entity.Ret_prd_cut;
import com.icim.entity.Ret_wo;

@Repository
public class RetPrdCutDao {

	@Resource
    private JdbcTemplate icimJdbcTemplate;
	
	public List<Ret_prd_cut> getCutListByPID(String pro_id)
	{
		String sql = "select * from ret_prd_cut where prd_seq_id='"+pro_id+"'";
		return icimJdbcTemplate.query(sql, new RowMapper<Ret_prd_cut>() {
			@Override
	          public Ret_prd_cut mapRow(ResultSet rs, int rowNum) throws SQLException {
				return setEntityByRs(rs);
			}
		});
	}
	
	public List<Ret_prd_cut> getCutListByPVID(String pro_id)
	{
		String sql = "select * from ret_prd_cut where pv_prd_seq_id='"+pro_id+"'";
		return icimJdbcTemplate.query(sql, new RowMapper<Ret_prd_cut>() {
			@Override
	          public Ret_prd_cut mapRow(ResultSet rs, int rowNum) throws SQLException {
				return setEntityByRs(rs);
			}
		});
	}
	
	public List<Ret_prd_cut> findSheets(List<Ret_wo> woList,Timestamp beginTimestamp, Timestamp endTimestamp)
    {
    	StringBuffer sqlBuffer = new StringBuffer();

		sqlBuffer.append(" SELECT * FROM RET_PRD_CUT ");
		sqlBuffer.append(" WHERE  EVT_TIMESTAMP >= ? AND EVT_TIMESTAMP <? ");
		sqlBuffer.append(" AND EVT_CATE ='NB2M' ");
		if (woList.isEmpty()) {
			return new ArrayList<Ret_prd_cut>();
		}

		sqlBuffer.append(" AND WO_ID_FK IN ( ");
		for (Ret_wo retWo : woList) {
			sqlBuffer.append("'").append(retWo.getWo_id()).append("'");
			if (woList.indexOf(retWo) < woList.size() - 1) {
				sqlBuffer.append(",");
			}
		}
		sqlBuffer.append(")");

		return icimJdbcTemplate.query(sqlBuffer.toString(), new Object[] {
				beginTimestamp, endTimestamp },
				new RowMapper<Ret_prd_cut>() {

					@Override
					public Ret_prd_cut mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						return setEntityByRs(rs);
					}
					
				});
    }
	
	public Ret_prd_cut setEntityByRs(ResultSet rs) throws SQLException
	{
		Ret_prd_cut ret_prd_cut = new Ret_prd_cut();
		ret_prd_cut.setPRD_SEQ_ID(rs.getString("PRD_SEQ_ID"));
		ret_prd_cut.setPRD_SEQ_TYP(rs.getString("PRD_SEQ_TYP"));
		ret_prd_cut.setBOX_ID_FK(rs.getString("BOX_ID_FK"));
		ret_prd_cut.setSLOT_NO(rs.getString("SLOT_NO"));
		ret_prd_cut.setMDL_TYP_FK(rs.getString("MDL_TYP_FK"));
		ret_prd_cut.setMDL_CATE_FK(rs.getString("MDL_CATE_FK"));
		ret_prd_cut.setMDL_ID_FK(rs.getString("MDL_ID_FK"));
		ret_prd_cut.setPV_PRD_SEQ_ID(rs.getString("PV_PRD_SEQ_ID"));
		ret_prd_cut.setPRD_QTY(rs.getInt("PRD_QTY"));
		ret_prd_cut.setPRD_STD_QTY(rs.getInt("PRD_STD_QTY"));
		ret_prd_cut.setPRD_GRADE(rs.getString("PRD_GRADE"));
		ret_prd_cut.setEVT_CATE(rs.getString("EVT_CATE"));
		ret_prd_cut.setEVT_TIMESTAMP(rs.getTimestamp("EVT_TIMESTAMP"));
		ret_prd_cut.setWO_ID_FK(rs.getString("WO_ID_FK"));
		ret_prd_cut.setSO_ID_FK(rs.getString("SO_ID_FK"));
		ret_prd_cut.setMTRL_PROD_ID_FK(rs.getString("MTRL_PROD_ID_FK"));
		ret_prd_cut.setMTRL_GRADE_FK(rs.getString("MTRL_GRADE_FK"));
		ret_prd_cut.setMTRL_LOT_ID_FK(rs.getString("MTRL_LOT_ID_FK"));
		ret_prd_cut.setMTRL_BOX_ID_FK(rs.getString("MTRL_BOX_ID_FK"));
		ret_prd_cut.setMTRL_PALLET_ID_FK(rs.getString("MTRL_PALLET_ID_FK"));
		ret_prd_cut.setPALLET_ID(rs.getString("PALLET_ID"));
		ret_prd_cut.setWH_IN_TIMESTAMP(rs.getTimestamp("WH_IN_TIMESTAMP"));
		ret_prd_cut.setSHIPPING_TIMESTAMP(rs.getTimestamp("SHIPPING_TIMESTAMP"));
		ret_prd_cut.setCR_OPE_ID_FK(rs.getString("CR_OPE_ID_FK"));
		return ret_prd_cut;
	}
}
